package test.com.googlecode.likesql;

import junit.framework.TestCase;

import com.googlecode.likesql.LikeSql;
import com.googlecode.likesql.dml.DML;
import com.googlecode.likesql.expression.ArrayExp;
import com.googlecode.likesql.expression.AtExp;
import com.googlecode.likesql.expression.LikeExp;
import com.googlecode.likesql.expression.QuestExp;

/**
 * @author Johny Huang
 *
 */
public class SelectSqlTester extends TestCase{

	public void testSimpleSQL(){
		LikeSql lSql=DML.selectAll()
		    .from("users")
		    .where(QuestExp.qt("name=? and id=? or height>?")
		    		.set(1,"tom")
                    .set(2, 1457)
                    .set(3,175.5))
            .orderBy("id")
		    .desc();
		
		String eSql="SELECT * FROM users WHERE name='tom' and id=1457 or height>175.5 ORDER BY id DESC";
		String eTpl="SELECT * FROM users WHERE name=? and id=? or height>? ORDER BY id DESC";
		
		assertEquals(eSql,lSql.toString());
		assertEquals(eTpl,lSql.toTemplate());
	}
	
	public void testEmbedSQL(){	
		LikeSql embedSql=DML.select("user_id")
		                          .from("users_account b")
		                          .where(QuestExp.qt("account>? and b.user_id=a.id").set(1,100));
		
		
		LikeSql sSql=DML.select("id,name")
	                          .from("users a")
	                          .where(QuestExp.qt("exists (?)")
	                        		         .set(1, embedSql))
	                          .orderBy("id")
	                          .asc();
		
		String eSql="SELECT id,name FROM users a WHERE exists (SELECT user_id FROM users_account b WHERE account>100 and b.user_id=a.id) ORDER BY id ASC";
		String eTpl="SELECT id,name FROM users a WHERE exists (SELECT user_id FROM users_account b WHERE account>? and b.user_id=a.id) ORDER BY id ASC";
	
	    assertEquals(eSql,sSql.toString());
		assertEquals(eTpl,sSql.toTemplate());
	}
	
	public void testLikeCriteria(){
		LikeSql sSql=DML.selectAll()
		    .from("user")
		    .where(QuestExp.qt("name like ?").set(1, LikeExp.like("T%d")));
		
		String eSql="SELECT * FROM user WHERE name like 'T%d'";
		String eTpl="SELECT * FROM user WHERE name like ?";
		
		assertEquals(eSql,sSql.toString());
		assertEquals(eTpl,sSql.toTemplate());
	}
	
	public void testLikeCriteria1(){
		LikeSql sSql=DML.selectAll()
		    .from("user")
		    .where(QuestExp.qt("name like ?").set(1, LikeExp.startWith("A")));
		
		String eSql="SELECT * FROM user WHERE name like '%A'";
		String eTpl="SELECT * FROM user WHERE name like ?";
		
		assertEquals(eSql,sSql.toString());
		assertEquals(eTpl,sSql.toTemplate());
	}
	
	public void testLikeCriteria2(){
		LikeSql sSql=DML.selectAll()
		    .from("user")
		    .where(QuestExp.qt("name like ?").set(1, LikeExp.inAnywhere("A")));
		
		String eSql="SELECT * FROM user WHERE name like '%A%'";
		String eTpl="SELECT * FROM user WHERE name like ?";
		
		assertEquals(eSql,sSql.toString());
		assertEquals(eTpl,sSql.toTemplate());
	}
	
	public void testGroupBy(){
		LikeSql sSql=DML.select("count(*) as num,gender")
		    .from("user")
		    .groupBy("gender");
		
		String eSql="SELECT count(*) as num,gender FROM user GROUP BY gender";
		String eTpl="SELECT count(*) as num,gender FROM user GROUP BY gender";
		
		assertEquals(eSql,sSql.toString());
		assertEquals(eTpl,sSql.toTemplate());
	}
	
	public void testInExpression(){
		LikeSql sSql=DML.select("id,name")
		    .from("user")
		    .where(QuestExp.qt("id in (?)").set(1,ArrayExp.array(new int[]{1,3,6})));
		
		String eSql="SELECT id,name FROM user WHERE id in (1,3,6)";
		String eTpl="SELECT id,name FROM user WHERE id in (?,?,?)";
		
		assertEquals(eSql,sSql.toString());
		assertEquals(eTpl,sSql.toTemplate());
	}
	
	public void testStringInExpression(){
		LikeSql sSql=DML.select("id,name")
		    .from("user")
		    .where(QuestExp.qt("name in (?)").set(1, ArrayExp.array(new String[]{"Stone","May","John"})));
		
		String eSql="SELECT id,name FROM user WHERE name in ('Stone','May','John')";
		String eTpl="SELECT id,name FROM user WHERE name in (?,?,?)";
		
		assertEquals(eSql,sSql.toString());
		assertEquals(eTpl,sSql.toTemplate());
	}
	
	public void testQuestStyleExpression(){
		LikeSql sql=DML.select("id,name")
		               .from("user")
		               .where(QuestExp.qt("(name =? or id=?) and gender=?")
		            		                       .set(2, 1)
		            		                       .set(1, "John")
		            		                       .set(3,"M"));
		
		String eSql="SELECT id,name FROM user WHERE (name ='John' or id=1) and gender='M'";
		String eTpl="SELECT id,name FROM user WHERE (name =? or id=?) and gender=?";
		
		assertEquals(eSql,sql.toString());
		assertEquals(eTpl,sql.toTemplate());
		
	}
	
	public void testQuestStyleExpression1(){
		LikeSql sql=DML.select("id,name")
		               .from("user")
		               .where(QuestExp.qt("(name =? or id=?) and gender=?")
		            		                       .set(2, 1)
		            		                       .set(1, "John")
		            		                       .set(3,"M")
		            		                       .set(5,"abc"));
		
		String eSql="SELECT id,name FROM user WHERE (name ='John' or id=1) and gender='M'";
		String eTpl="SELECT id,name FROM user WHERE (name =? or id=?) and gender=?";
		
		assertEquals(eSql,sql.toString());
		assertEquals(eTpl,sql.toTemplate());	
	}
	
	public void testQuestStyleExpression2(){
		LikeSql sql=DML.select("id,name")
		               .from("user")
		               .where(QuestExp.qt("(name =? or id=?) and gender=?")
		            		                       .set(2, 1)
		            		                       .set(3,"M"));
		
		String eSql="SELECT id,name FROM user WHERE (name =? or id=1) and gender='M'";
		String eTpl="SELECT id,name FROM user WHERE (name =? or id=?) and gender=?";
		
		assertEquals(eSql,sql.toString());
		assertEquals(eTpl,sql.toTemplate());
	}
	
	public void testQuestStyleExpression3(){
		LikeSql sql=DML.select("id,name")
		               .from("user")
		               .where(QuestExp.qt("(name =? or id=?) and gender=?")
		            		                       .set(2, 1)
		            		                       .set(1, "John"));
		
		String eSql="SELECT id,name FROM user WHERE (name ='John' or id=1) and gender=?";
		String eTpl="SELECT id,name FROM user WHERE (name =? or id=?) and gender=?";
		
		assertEquals(eSql,sql.toString());
		assertEquals(eTpl,sql.toTemplate());
	}
	
	public void testAtStyleExpression(){
		LikeSql sql=DML.select("id,name")
		               .from("user")
		               .where(AtExp.at("(name =@name or id=@id) and gender=@gender")
		            		                       .set("id", 1)
		            		                       .set("name", "John")
		            		                       .set("gender", "M"));
		
		String eSql="SELECT id,name FROM user WHERE (name ='John' or id=1) and gender='M'";
		String eTpl="SELECT id,name FROM user WHERE (name =? or id=?) and gender=?";
		
		assertEquals(eSql,sql.toString());
		assertEquals(eTpl,sql.toTemplate());
	}
	
	public void testAtStyleExpression1(){
		LikeSql sql=DML.select("id,name")
		               .from("user")
		               .where(AtExp.at("(name =@name or id=@id) and gender=@gender")
		            		                       .set("name", "John")
		            		                       .set("gender", "M"));
		
		String eSql="SELECT id,name FROM user WHERE (name ='John' or id=@id) and gender='M'";
		String eTpl="SELECT id,name FROM user WHERE (name =? or id=@id) and gender=?";
		
		assertEquals(eSql,sql.toString());
		assertEquals(eTpl,sql.toTemplate());
	}
}
